package cn.com.se.其他工具;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.Date;
import java.util.UUID;

/**
 * @author lingheng
 * @version 1.0
 * @description: TODO
 * @date 2023-12-4 18:40
 */
public class ExcelToSql {
    public static void main(String[] args) {
        String excelFilePath = "D:\\export_result.xlsx"; // 替换为您的Excel文件路径
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = new XSSFWorkbook(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(2); // 假设数据在第一个工作表
            for (Row row : sheet) {
                // 跳过标题行
                if (row.getRowNum() == 0) {
                    continue;
                }
                String tutorId = row.getCell(0).getStringCellValue(); // 假设导师ID在第一列
                String pupilId = row.getCell(1).getStringCellValue(); // 徒弟ID
                String tutorEmployeeId = row.getCell(2).getStringCellValue(); // 假设导师员工ID在第二列
                String pupilEmployeeId = row.getCell(3).getStringCellValue(); // 徒弟员工ID
                Date startDate = row.getCell(15).getDateCellValue(); // 任务开始时间
                Date endDate = row.getCell(16).getDateCellValue(); // 任务结束时间

                帮助做回顾(tutorId, tutorEmployeeId, pupilId, pupilEmployeeId, DateTimeUtils.formatDate(startDate,"yyyy-MM-dd hh:mm:ss"), DateTimeUtils.formatDate(endDate,"yyyy-MM-dd hh:mm:ss"));
                客观给评价(tutorId, tutorEmployeeId, pupilId, pupilEmployeeId, DateTimeUtils.formatDate(startDate,"yyyy-MM-dd hh:mm:ss"), DateTimeUtils.formatDate(endDate,"yyyy-MM-dd hh:mm:ss"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void 帮助做回顾(String tutorId, String tutorEmployeeId, String pupilId, String pupilEmployeeId, String startDate, String endDate) {
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");
        String sql = "INSERT INTO hr.tutor_task (ID, TASK_TYPE, TASK_NAME, TUTOR_ID, TUTOR_EMPLOYEE_ID, ACTION_ID, TASK_DESC, TASK_REQUIRE, TASK_STATUS, TASK_COMPLETION, TASK_EVIDENCE, TASK_SUBMIT_DATE, TASK_BEGIN_DATE, TASK_END_DATE, UPDATE_DATE, CREATE_DATE, FINISH_FLAG, DELETE_REASON, DEL_FLAG) " +
                "VALUES ('" + uuid + "', 'HELP_LOOK_BACK', '帮助做回顾', '" + tutorId + "', '" + tutorEmployeeId + "', '14', '在徒弟面临转正之际，面临转正了哦~导师与徒弟进行一次一对一的交流回顾，可以了解徒弟对目前实际工作的适应情况、参加百羊班之后的理解度、对公司制度的理解度等，以此进一步加深徒弟对公司文化及制度文化的理解,促进对组织的认同感。', '日历邀约记录或与徒弟的飞书邀约记录(需体现徒弟接受日程)', 'UNSUBMITTED', NULL, NULL, NULL, TO_TIMESTAMP('" + startDate + "','SYYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP('" + endDate + "','SYYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP('2023-12-05 00:00:00','SYYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP('2023-12-05 00:00:00','SYYYY-MM-DD HH24:MI:SS:FF6'), '01', NULL, '0');";
        String pupilUuid = UUID.randomUUID().toString().replaceAll("-", "");
        String sql1 = "INSERT INTO HR.TUTOR_TASK_RELATION (ID, TASK_ID, PUPIL_ID, UPDATE_DATE, CREATE_DATE, DEL_FLAG, EMPLOYEE_ID) " +
                "VALUES('" + pupilUuid + "', '" + uuid + "', '" + pupilId + "', TIMESTAMP '2023-12-05 00:00:00', TIMESTAMP '2023-12-05 00:00:00', '0', '" + pupilEmployeeId + "');";
        System.out.println(sql);
        System.out.println(sql1);
    }


    private static void 客观给评价(String tutorId, String tutorEmployeeId, String pupilId, String pupilEmployeeId, String startDate, String endDate) {
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");
        String sql = "INSERT INTO hr.tutor_task (ID, TASK_TYPE, TASK_NAME, TUTOR_ID, TUTOR_EMPLOYEE_ID, ACTION_ID, TASK_DESC, TASK_REQUIRE, TASK_STATUS, TASK_COMPLETION, TASK_EVIDENCE, TASK_SUBMIT_DATE, TASK_BEGIN_DATE, TASK_END_DATE, UPDATE_DATE, CREATE_DATE, FINISH_FLAG, DELETE_REASON, DEL_FLAG) " +
                "VALUES ('" + uuid + "', 'OBJECTIVE_EVALUATE', '客观给评价', '" + tutorId + "', '" + tutorEmployeeId + "', '26', '徒弟试用期结束前15天，导师应基于对徒弟的带教了解，对徒弟的“味道”及公司的融入度、适应度进行书面客观评估并反馈徒弟的直接上级，向公司提供导师视角对新员工文化匹配度的客观评价，作为对徒弟转正的参考意见。','转正前导师需提交客观评价表先飞书或当面反馈给徒弟的上级，反馈后再提交至系统\"飞书-我的家族\"', 'UNSUBMITTED', NULL, NULL, NULL, TO_TIMESTAMP('" + startDate + "','SYYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP('" + endDate + "','SYYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP('2023-12-05 00:00:00','SYYYY-MM-DD HH24:MI:SS:FF6'), TO_TIMESTAMP('2023-12-05 00:00:00','SYYYY-MM-DD HH24:MI:SS:FF6'), '01', NULL, '0');";
        String pupilUuid = UUID.randomUUID().toString().replaceAll("-", "");
        String sql1 = "INSERT INTO HR.TUTOR_TASK_RELATION (ID, TASK_ID, PUPIL_ID, UPDATE_DATE, CREATE_DATE, DEL_FLAG, EMPLOYEE_ID) " +
                "VALUES('" + pupilUuid + "', '" + uuid + "', '" + pupilId + "', TIMESTAMP '2023-12-05 00:00:00', TIMESTAMP '2023-12-05 00:00:00', '0', '" + pupilEmployeeId + "');";
        System.out.println(sql);
        System.out.println(sql1);
    }
}